#!/user/bin/env jython
# -*- coding: utf-8 -*-

import csv
import sys
import os
from com.ziclix.python.sql import zxJDBC

########################################################################

#os.system("export CLASSPATH=`pwd`/sqlitejdbc-v056.jar")

print sys.getdefaultencoding()

DATABASE    = "Rebides.db"
JDBC_URL    = "jdbc:sqlite:%s"  % DATABASE
JDBC_DRIVER = "org.sqlite.JDBC"

########################################################################

def main():
	
	
	dbConn = getConnection(JDBC_URL, JDBC_DRIVER)
    
	cursor = dbConn.cursor()
	
	cursor.execute('''create table if not exists info (codProf integer, nome text, grau text, curso text, categoria text, regime text, ano_lectivo integer, tipo_ensino text, estabelecimento text)''')
	
	for x in range(0, 11):
	   
		
		spamReader = csv.reader(open('csv/reb' + str(x) + '.csv', 'rb'), delimiter=',')

		for row in spamReader:
		
			
			
			if str(row[0]) == "Código do Docente":
				print "nao imprimo"
				continue
			else:
				
				stmt = '''insert into info (codProf, nome, grau, curso, categoria, regime, ano_lectivo, tipo_ensino, estabelecimento) values ("%s", "%s","%s","%s","%s","%s","%s","%s","%s")''' % ((unicode(str(row[0]), 'utf-8').replace('\"', ' '),\
				unicode(str(row[1]), 'utf-8').replace('\"', ' '),\
				unicode(str(row[2]), 'utf-8').replace('\"', ' '),\
				unicode(str(row[3]), 'utf-8').replace('\"', ' '),\
				unicode(str(row[4]), 'utf-8').replace('\"', ' '),\
				unicode(str(row[5]), 'utf-8').replace('\"', ' '),\
				unicode(str(row[6]), 'utf-8').replace('\"', ' '),\
				unicode(str(row[7]), 'utf-8').replace('\"', ' '),\
				unicode(str(row[8]), 'utf-8').replace('\"', ' ')))
        
				cursor.execute(stmt)
				pass
			pass
	
	
	dbConn.commit()
	cursor.close()
	dbConn.close()
	sys.exit(0)

################################################################################

def getConnection(jdbc_url, driverName):
    """
        Given the name of a JDBC driver class and the url to be used 
        to connect to a database, attempt to obtain a connection to 
        the database.
    """

    try:
        # no user/password combo needed here, hence the None, None
        dbConn = zxJDBC.connect(jdbc_url, None, None, driverName)
    except zxJDBC.DatabaseError, msg:
        print msg
        sys.exit(-1)

    return dbConn

################################################################################
################################################################################

if __name__ == '__main__':
    main()
